<?php

include("../../header.inc");
require_once("../../DataBase.inc");
include("calculation.inc");
require_once("../../general_queries.inc");

$programs = query_programs()->execute();
$subjects = DataBase::select("subject", null, array("selection_campaign_id" => $_SESSION["campaign_id"]));
foreach ($programs as $p) {
    if (isset($_GET["program_num"]) && $p["program_id"] == $_GET["program_num"]) {// the get value of program number is more that array of program id in DB
        echo "<br/>";
        echo "Apply rules for program <b>" . $p["program_name"] . "</b><br/>";
        $rules = DataBase::select("exam_eligibility_rule", null, array("selection_campaign_id" => $_SESSION["campaign_id"], "program_id" => $p["program_id"]));
        $rules_subjects = array();
        foreach ($rules as $rule) {
            $rule_id = $rule["exam_eligibility_rule_id"];
            $rule_subjects = DataBase::select("exam_eligibility_rule_subjects", null, array("exam_eligibility_rule_id" => $rule_id));
            $rules_subjects[$rule_id] = $rule_subjects;
        }
        $q_eligibles = query_eligibles_for_program($p["program_id"], $rules, $rules_subjects);
        $q_allmarks = query_applicants_having_all_marks($subjects);
        $q = "SELECT APPLICANT_ID FROM " . $q_eligibles;
	echo " + Update eligibility of applicants having their eligibility already set:<br/>";
	echo " + + Update eligibles: ";
        DataBase::query("UPDATE applicant_eligibility SET eligible=1 WHERE applicant_eligibility.program_id=" . $p["program_id"] . " AND applicant_eligibility.applicant_id IN (" . $q . ")");
        if (DataBase::last_error_number() <> 0)
            echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
	else echo DataBase::number_of_affected_rows()." applicants updated.<br/>";
	echo " + + Remove exclusion for eligibles previously excluded because of non-eligibility: ";
        DataBase::query("UPDATE applicant SET excluded=0 WHERE excluded=1 AND excluded_reason='Not eligible for any program' AND applicant.applicant_id IN (" . $q . ")");
        if (DataBase::last_error_number() <> 0)
            echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
//	else echo DataBase::number_of_affected_rows()." applicants back in the process.<br/>";
//	echo " + + Update non-eligibles: ";
        DataBase::query("UPDATE applicant_eligibility SET eligible=0 WHERE applicant_eligibility.program_id=" . $p["program_id"] . " AND applicant_eligibility.applicant_id NOT IN (" . $q . ") AND applicant_eligibility.applicant_id IN (" . $q_allmarks . ")");
        if (DataBase::last_error_number() <> 0)
            echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
//	else echo DataBase::number_of_affected_rows()." applicants updated.<br/>";

        $all = "SELECT applicant_id FROM applicant WHERE selection_campaign_id=" . $_SESSION["campaign_id"] . " AND excluded=0";

//	echo " + Insert eligible applicants: ";
        DataBase::query("INSERT INTO applicant_eligibility (applicant_id,program_id,eligible) SELECT APPLICANT_ID," . $p["program_id"] . ",1 FROM (SELECT APPLICANT_ID FROM (SELECT APPLICANT_ID FROM " . $q_eligibles . ") AS ELIGIBLES WHERE ELIGIBLES.APPLICANT_ID NOT IN (SELECT applicant_id FROM applicant_eligibility WHERE program_id=" . $p["program_id"] . ")) AS TO_INSERT");
        if (DataBase::last_error_number() <> 0)
            echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
//	else echo DataBase::number_of_affected_rows()." applicants inserted.<br/>";
//	echo " + Insert non-eligible applicants: ";
        DataBase::query("INSERT INTO applicant_eligibility (applicant_id,program_id,eligible) SELECT applicant_id," . $p["program_id"] . ",0 FROM applicant WHERE selection_campaign_id=" . $_SESSION["campaign_id"] . " AND applicant.applicant_id NOT IN (SELECT applicant_id FROM applicant_eligibility WHERE program_id=" . $p["program_id"] . ") AND applicant.applicant_id IN (" . $q_allmarks . ")");
        if (DataBase::last_error_number() <> 0)
            echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
//	else echo DataBase::number_of_affected_rows()." applicants inserted.<br/>";
    }
}
echo "<br/>Eligibility rules applied.<br/>";

echo "<br/>Exclude applicants non-eligible for any programs: ";
$q = "UPDATE applicant SET excluded=1,excluded_reason='Not eligible for any program' WHERE selection_campaign_id=" . $_SESSION["campaign_id"];
foreach ($programs as $p) {
    if (isset($_GET["program_number"]) && $p["program_id"] == $_GET["program_number"]) {// the get value of program number is more that array of program id in DB
        $q .= " AND applicant_id IN (SELECT applicant_id FROM applicant_eligibility WHERE program_id=" . $p["program_id"] . " AND eligible=0)";
    }
}
DataBase::query($q);
if (DataBase::last_error_number() <> 0)
    echo "Error #" . DataBase::last_error_number() . ": " . DataBase::last_error_message() . "<br/>";
else
    echo DataBase::number_of_affected_rows() . " applicants excluded.<br/>";

$query = "SELECT COUNT(*) FROM applicant";
$query .= " LEFT JOIN applicant_more_info ON applicant_more_info.applicant_id=applicant.applicant_id";
for ($i = 0; $i < count($subjects); $i++)
    $query .= " LEFT JOIN applicant_score_subject_exam AS score_" . $i . " ON (score_" . $i . ".applicant_id=applicant.applicant_id AND score_" . $i . ".subject_id=" . $subjects[$i]["subject_id"] . ")";
$query .= " WHERE selection_campaign_id=" . $_SESSION["campaign_id"] . " AND applicant_more_info_attendee_exam_status=1";
$query .= " AND (";
for ($i = 0; $i < count($subjects); $i++) {
    if ($i > 0)
        $query .= " OR ";
    $query .= "score_" . $i . ".score_subject_exam IS NULL";
}
$query .= ")";
$result = DataBase::query($query);
$nbNotFullyOk = $result[0][0];

if ($nbNotFullyOk > 0)
    echo "<br/><b>Note: rules have been applied only to applicants having all their marks in the system: <span style='color:red'>" . $nbNotFullyOk . " do not have currently</span>.</b><br/>";

include("../../footer.inc");
?>
